/*
 * Copyright (c) $today.year-Now http://www.zxpnet.com All rights reserved.
 */

package com.zxp.springboot.util;

import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.google.common.base.CaseFormat;
import com.google.common.collect.Lists;
import com.zxp.springboot.dto.Db;
import com.zxp.springboot.pojo.ColumnClass;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库元数据的操作工具类， 更多元数据的操作测试，见：com.zxp.springboot.DataSourceMetaDataTest
 * @desc:
 * @author: shollin
 * @date: 2021/6/15/015 20:23
 */
@Slf4j
public class DbUtil {

    private static ThreadLocal<Connection> connectionTl = new ThreadLocal<Connection>();
    private static Connection connection;

    /**
     * 初始化
     *
     * @param db
     * @return
     */
    public static Connection initDB(Db db) throws ClassNotFoundException, SQLException {

        String driver = StrUtil.emptyToDefault(db.getDriver(), "com.mysql.cj.jdbc.Driver");
        //
        String url = StrUtil.format("jdbc:mysql://{}:{}/{}?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=CONVERT_TO_NULL&serverTimezone=Asia/Shanghai",
                StrUtil.emptyToDefault(db.getHost(), "localhost"), ObjectUtil.defaultIfNull(db.getPort(), 3306), db.getDbname());

        String username = db.getUsername();
        String password = db.getPassword();
        log.debug("数据库连接信息:"+db);

        //classLoader,加载对应驱动
        Class.forName(driver);
        connection = (Connection) DriverManager.getConnection(url, username, password);
        connectionTl.set(connection);

        return connection;
    }

    public static Connection getConnection() {
        return connection;
    }

    /**
     * 获取所有的表名
     *
     * @param connection
     * @return
     * @throws SQLException
     */
    public static List<String> getTableNames(Connection connection) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        String catalog = connection.getCatalog();
        ResultSet tables = metaData.getTables(catalog, null, null, null);
        List<String> tableNameLists = new ArrayList<>();
        while (tables.next()) {
            String table_name = tables.getString("TABLE_NAME");
            // 将表转成驼峰形式
            String modelName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, table_name);
            tableNameLists.add(table_name);
        }
        log.debug(StrUtil.format("获取数据库:{} 的所有表格,{}",catalog,tableNameLists));
        return tableNameLists;
    }

    /**
     * 获取表的所有列
     *
     * @return
     */
    public static List<ColumnClass> getColumns(String tableName) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        // 找出表的所有列
        //catalog 其实也就是数据库名
        final String catalog = connection.getCatalog();
        ResultSet columns = metaData.getColumns(catalog, null, tableName, null);
        // 找出表的主键，有可能有多个
        ResultSet primaryKeys = metaData.getPrimaryKeys(catalog, null, tableName);

        ArrayList<ColumnClass> columnLists = Lists.newArrayList();
        while (columns.next()) {
            String column_name = columns.getString("COLUMN_NAME");
            String type_name = columns.getString("TYPE_NAME");
            String remarks = columns.getString("REMARKS");
            ColumnClass columnClass = new ColumnClass();
            columnClass.setRemark(remarks);
            columnClass.setColumnName(column_name);
            columnClass.setType(type_name);
            columnClass.setPropertyName(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.UPPER_CAMEL, column_name));
            columnClass.setPrimary(false);
            columnLists.add(columnClass);

        }
        while (primaryKeys.next()) {
            String pkName = primaryKeys.getString("COLUMN_NAME");
            if (StrUtil.isNotBlank(pkName)) {
                columnLists.stream().forEach(columnClass -> {
                    if (pkName.equals(columnClass.getColumnName())) {
                        columnClass.setPrimary(true);
                    }
                });
            }
        }
        log.debug(StrUtil.format("获取{}数据表:{} 的所有列",catalog,tableName));
        return columnLists;
    }

    /**
     * # 查询test数据库里面所有的表信息*, 如table_name,table_comment
     * SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test';

     * #查询test数据库user表的所有字段信息*, 如column_name, data_type,column_comment
     * SELECT	column_name,	data_type,column_comment FROM information_schema.COLUMNS
     * WHERE	TABLE_SCHEMA = 'test'	AND TABLE_NAME = 'user';
     * @param args
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Db db = new Db();
        db.setDriver("");
        db.setPort(3306);
        db.setHost("localhost");
        db.setDbname("demo");
        db.setUsername("root");
        db.setPassword("123456");

        Connection connection = initDB(db);
        List<String> tableNames = getTableNames(connection);
        //List<ColumnClass> configInfo = getColumns("his_config_info");
        List<ColumnClass> configInfo = getColumns("tenant_capacity");
        System.out.println(tableNames);
        System.out.println(configInfo);
    }
}
